/**
 * 
 */
package com.gkwave.module.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.gkwave.module.bean.BloodRequirementBean;
import com.gkwave.module.bean.BranchBean;
import com.gkwave.module.bean.FoodMenuBean;
import com.gkwave.module.bean.HealthCareDoctorBean;
import com.gkwave.module.bean.LocationBean;

/**
 * @author Administrator
 *
 */
public class HealthCareDAO extends BaseDAO {

	private Statement stmt = null;
	private ResultSet rset = null;
	private Connection con = null;
	/**
	 * 
	 */
	public HealthCareDAO() {
		// TODO Auto-generated constructor stub
	}

	public HealthCareDoctorBean getAllDoctorList() {
		// TODO Auto-generated method stub
		String query = "SELECT t1.id_specialist_doctor," +
							  "t1.nm_specialist_doctor," +
							  "t2.nm_medical_specialist," +
							  "t1.dt_from," +
							  "t1.dt_to," +
							  "t1.ts_from," +
							  "t1.ts_to," +
							  "t1.nm_contact_number," +
							  "t1.id_email," +
							  "t3.nm_branch," +
							  "t4.nm_location " +
							  "FROM specialist_doctor t1,m_medical_specialist t2,m_branch t3,m_location t4 where t1.id_medical_specialist=t2.id_medical_specialist and t1.id_branch=t3.id_branch and t4.id_location=t3.id_location" ;
		
		System.out.println("Query: " + query);
		BaseDAO dao = null;
		HealthCareDoctorBean xbean = null;
		try {
			dao = new BaseDAO();
			con = dao.openConnection();
			stmt = con.createStatement();
			rset = stmt.executeQuery(query);
			HealthCareDoctorBean bean = null;
			
			if(!rset.wasNull()){
				System.out.println("result-set is not null");
				xbean = new HealthCareDoctorBean();
			}
			while (rset.next()) {
				Integer idSpecialistDoctor = rset.getInt("t1.id_specialist_doctor");
				String nmSpecialistDoctor = rset.getString("t1.nm_specialist_doctor");
				String nmMedicalSpecialist = rset.getString("t2.nm_medical_specialist");
				String dtFrom = rset.getString("t1.dt_from");
				String dtTo = rset.getString("t1.dt_to");
				String tsFrom = rset.getString("t1.ts_from");
				String tsTo = rset.getString("t1.ts_to");
				String nmContactNumber = rset.getString("t1.nm_contact_number");
				String idEmail = rset.getString("t1.id_email");
				String nmBranch = rset.getString("t3.nm_branch");
				String nmLocation = rset.getString("t4.nm_location");
				
				bean = new HealthCareDoctorBean(
												idSpecialistDoctor,
												nmSpecialistDoctor,
												nmMedicalSpecialist,
												dtFrom,
												dtTo,
												tsFrom,
												tsTo,
												nmContactNumber,
												idEmail,
												nmBranch,
												nmLocation
											   );
				xbean.entries.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				dao.closeConnection(stmt, con , rset);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return xbean;
	}
	
	public BloodRequirementBean getBloodRequirementDetailList(){
		String query = "SELECT t1.nm_blood_group, " +
				              "t1.nr_unit_required, " +
				              "t1.nm_patient, " +
				              "t1.nm_hospital, " +
				              "t2.nm_location, " +
				              "t1.id_patient, " +
				              "t1.nm_contact, " +
				              "t1.dt_date, " +
				              "t1.ts_timing, " +
				              "t1.txt_description " +
				       "FROM blood_requirement t1,m_location t2 WHERE t2.id_location=t1.id_location";
		System.out.println("Query: " + query);
		BaseDAO dao = null;
		BloodRequirementBean xbean = null;
		try {
			dao = new BaseDAO();
			con = dao.openConnection();
			stmt = con.createStatement();
			rset = stmt.executeQuery(query);
			BloodRequirementBean bean = null;
			
			if(!rset.wasNull()){
				System.out.println("result-set is not null");
				xbean = new BloodRequirementBean();
			}
			while (rset.next()) {
				String nmBloodGroup = rset.getString("t1.nm_blood_group");
				Integer nrUnitRequired = rset.getInt("t1.nr_unit_required");
				String nmPatient = rset.getString("t1.nm_patient");
				String nmHospital = rset.getString("t1.nm_hospital");
				String nmLocation = rset.getString("t2.nm_location");
				String idPatient = rset.getString("t1.id_patient");
				String nmContact = rset.getString("t1.nm_contact");
				String dtDate = rset.getString("t1.dt_date");
				String tsTiming = rset.getString("t1.ts_timing");
				String txtDescription = rset.getString("t1.txt_description");
				
				bean = new BloodRequirementBean(nmBloodGroup,
												nrUnitRequired,
												nmPatient,
												nmHospital,
												nmLocation,
												idPatient,
												nmContact,
												dtDate,
												tsTiming,
												txtDescription);
				xbean.entries.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				dao.closeConnection(stmt, con , rset);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return xbean;
	}

	public List<LocationBean> getLocationList(){
		String query = "SELECT id_location,nm_location from m_location";
		System.out.println("Query: " + query);
		LocationBean location = null;
		BaseDAO dao = new BaseDAO();
		List<LocationBean> list = null;
		try {
			con = dao.openConnection();
			stmt = con.createStatement();
			rset = stmt.executeQuery(query);
			if(!rset.wasNull()){
				list = new ArrayList<LocationBean>();
			}
			while (rset.next()) {
				Integer idLocation = rset.getInt("id_location");
				String nmLocation = rset.getString("nm_location");

				location = new LocationBean(idLocation,nmLocation);
				list.add(location);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				dao.closeConnection(stmt, con, rset);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;		
	}
	
	public List<BranchBean> getBranchesByLocation(Integer idLocation) {
		// TODO Auto-generated method stub
		String query = "SELECT t.id_branch,t.nm_branch FROM m_branch t where t.id_location='" + idLocation +"'" ;
		System.out.println("Query: " + query);
		BaseDAO dao = null;
		List<BranchBean> list = null;
		try {
			dao = new BaseDAO();
			con = dao.openConnection();
			stmt = con.createStatement();
			rset = stmt.executeQuery(query);
			BranchBean bean = null;
			list = new ArrayList<BranchBean>();
			while (rset.next()) {
				Integer idBranch = rset.getInt("t.id_branch");
				String nmBranch = rset.getString("t.nm_branch");
				bean = new BranchBean(idBranch,nmBranch);
				list.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				dao.closeConnection(stmt, con , rset);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}
	
	public boolean saveBloodDetail(String nm_blood_group, 
								   String nr_unit_required,
								   String nm_patient, 
								   String nm_hospital,
								   String id_location,
								   String id_patient,
								   String nm_contact,
								   String dt_date,
								   String ts_timing,
								   String txt_description) {
		// TODO Auto-generated method stub
		String query = "INSERT INTO blood_requirement  (nm_blood_group,nr_unit_required,nm_patient,nm_hospital,id_location,id_patient,nm_contact,dt_date,ts_timing,txt_description,in_active) VALUES " +
					   "('"+ nm_blood_group + "','" + nr_unit_required + "','" + nm_patient + "','"+ nm_hospital + "','"+ id_location + "','"+ id_patient + "','"+ nm_contact + "','"+ dt_date + "','"+ ts_timing + "','"+ txt_description + "','Y')";
		System.out.println(query);
		boolean returnValue = false;
		BaseDAO dao = new BaseDAO();
		try {
			con = dao.openConnection();
			stmt = con.createStatement();
			int row = stmt.executeUpdate(query);
			if (row != 0) {
				returnValue = true;
			}
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
			returnValue = false;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			returnValue = false;
		} finally {
			try {
				dao.closeConnection(stmt, con);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return returnValue;
	}	
}
